/**
 * 数据库dao层 统一对必要参数做校验 
 * 返回码约束
 * 0 成功   {code ,data}  对于查询没有数据 使用 {code ,null}
 * 1 参数校验失败 {code ,data}
 * 2 执行db失败 {code,data}
 */

var mysql=require("mysql");  
var crypto = require('../utils/crypto');

var pool = null;



function nop(a,b,c,d,e,f,g){

}
  
function query(sql,callback){  
    pool.getConnection(function(err,conn){  
        if(err){  
            callback(err,null,null);  
        }else{  
            conn.query(sql,function(qerr,vals,fields){  
                //释放连接  
                conn.release();  
                //事件驱动回调  
                callback(qerr,vals,fields);  
            });  
        }  
    });  
};

exports.init = function(config){
    pool = mysql.createPool({  
        host: config.HOST,
        user: config.USER,
        password: config.PSWD,
        database: config.DB,
        port: config.PORT,
    });
};

exports.agent_login=function(openid,nickname,sex,headimgurl,unionid,agent,callback){
	
    //1.如果agent不为空 ，表示邀请代理业务，没有就生成代理，有就直接登录
    //2. 代理商不存在  是否包含父节点 和代理商角色信息  如果没有 返回不是代理商
    //3. 若包含 父节点  创建 ，创建需要知道 父节点 和代理商角色等级
    //4 以上2中情况 同意返回 代理id 即可 后续流程由客户端发起

    callback = callback == null? nop:callback;

    if(openid == null ||nickname==null  ){
        callback({code:1,data:'param  is null'});
        return;
    }
    //未作关联
    if(unionid==null){
        console.log("只有在用户将公众号绑定到微信开放平台帐号后，才会出现该字段");
        callback({code:1,data:'param  unionid is null'});
        return 
    }
   
      // account 也使用unionid  统一起来
     if(unionid){
       openid = 'wx_' + unionid ;
        openid = '"' + openid +'"';
    }


     if(unionid){
        unionid = '"' + unionid + '"';
    }

   

     if(headimgurl){
        headimgurl = '"' + headimgurl + '"';
    }
    else{
        headimgurl = 'null';
    }

    nickname = crypto.toBase64(nickname);

     if(nickname){
        nickname = '"' + nickname + '"';
    }

    var sql = 'CALL p_agent_login({0},{1},{2},{3},{4},{5}, @a, @b,@c);';
    sql = sql.format(openid,nickname,sex,headimgurl,agent,unionid);

    query(sql, function(err, rows, fields) {
        if (err) {
            //throw err;
          callback({code:2,data :err});
           return;
        }

      //  console.log(rows);
        if(rows.length == 0){
            callback({code:0 ,data :null});
            return;
        }
        callback({code:0,data:rows[0]});
    });  


};
exports.is_user_exist = function(account,callback){
    callback = callback == null? nop:callback;
    if(account == null){
        callback({code:1,data:'param account is null'});
        return;
    }

    var sql = 'SELECT user_wx_union_id,role_id FROM tb_user WHERE user_wx_union_id = "' + account + '" limit 1';
    query(sql, function(err, rows, fields) {
        if (err) {
           // throw err;
           callback({code:2,data :err});
           return;
        }

        if(rows.length == 0){
            callback({code:0 ,data :null});
            return;
        }

        callback({code:0,data:row[0]});
    });  
};


//新增用户
exports.create_user = function(userId,openid,unionid,name,headimg,user_gold,game_id,callback){
    callback = callback == null? nop:callback;
    if(userId == null  || user_gold==null){
        callback({code :1 ,data : 'params is null'});
        console.log("创建用户 db_create_user 参数校验失败");
        return;
    }
    if(headimg){
        headimg = '"' + headimg + '"';
    }
    else{
        headimg = 'null';
    }
    name = crypto.toBase64(name);
    var sql = "";
    console.log(openid + "--" + unionid);
    if(openid==null || unionid==null){
        //游戏平台同步玩家数据生成去除微信id
        sql = 'INSERT INTO tb_user(user_id,user_wx_name,user_wx_headimgurl,user_gold,game_id,role_id,create_time,update_time) VALUES("{0}","{1}",{2},{3},{4},5,now(),now())';
        sql = sql.format(userId,name,headimg,user_gold,game_id);
    }else{
         sql = 'INSERT INTO tb_user(user_id,user_wx_open_id,user_wx_union_id,user_wx_name,user_wx_headimgurl,user_gold,game_id,role_id,create_time,update_time) VALUES("{0}","{1}",{2},{3},{4},{5},{6},5,now(),now())';
         sql = sql.format(userId,openid,unionid,name,headimg,user_gold,game_id);
    }
    console.log("create user:" + sql);
    query(sql, function(err, rows, fields) {
        if (err) {
           // throw err;
           callback({code:2,data:err});
           return;
        }
         var affectedRows = rows.affectedRows;
        callback({code:0,data:affectedRows});
    });
};


//修改用户
exports.update_user_info = function(unionid,name,headimg,callback){
    callback = callback == null? nop:callback;
    if(unionid == null){
        callback({code:1,data:'param unionid is null'});
        return;
    }
 
    if(headimg){
        headimg = '"' + headimg + '"';
    }
    else{
        headimg = 'null';
    }
    name = crypto.toBase64(name);
    var sql = 'UPDATE tb_user SET user_wx_name="{0}",user_wx_headimgurl={1} WHERE user_wx_union_id="{3}"';
    sql = sql.format(name,headimg,unionid);
    query(sql, function(err, rows, fields) {
        if (err) {
            //throw err;
            callback({code:2,data:err});
            return;
        }
        callback({code:0,data:1});
    });
};

exports.login_userid= function(userId,pwd,callback){

     callback = callback == null? nop:callback;
    if(userId == null){
        callback({code:1,data:'params userId is null'});
        return;
    }
   // var sql = 'SELECT id,pid,,user_id,user_wx_union_id,user_wx_name,user_wx_headimgurl,user_gold,role_id,(SELECT `name` from tb_role r where r.id = role_id) as role_name FROM tb_user WHERE id={0} LIMIT 1';
   var sql ='select ta.id,ta.user_id,ta.role_id from tb_agent ta WHERE  ta.user_id = {0} and ta.invite_code = {1} LIMIT 1'; 
   
   sql = sql.format(userId,pwd);
   
    query(sql, function(err, rows, fields) {
        if (err) {
           // throw err;
           callback({code:2,data:err});
           return;
        }
        if(rows.length >0){
            callback({code:0,data:rows[0]});
        }else{
            callback({code:0,data:null});
        }
    });
};

//获取用户基本信息
exports.get_user_base_info = function(id,callback){
    callback = callback == null? nop:callback;
    if(id == null){
        callback({code:1,data:'params id is null'});
        return;
    }
   var sql ='select ta.id,ta.invite_code,tu.userid,tu.`name`,tu.headimg,tu.gems,ta.role_id from t_users tu ,tb_agent ta WHERE tu.userid = ta.user_id AND ta.id = {0} LIMIT 1';
   sql = sql.format(id);
    console.log("获取用户基本信息 db-getUserBaseInfo :"  +  sql);
    query(sql, function(err, rows, fields) {
        if (err) {
           // throw err;
           callback({code:2,data:err});
           return;
        }
        if(rows.length >0){
            rows[0].name = crypto.fromBase64(rows[0].name);
            callback({code:0,data:rows[0]});
        }else{
            callback({code:0,data:null});
        }
    });
};

exports.get_static_data =function(userId,role_id,callback){
      callback = callback == null? nop:callback;
    if(userId == null){
        callback({code:1,data:'params id is null'});
        return;
    }

    var sql = 'SELECT count(1) as total FROM tb_agent ta WHERE ta.pid={0} UNION all SELECT count(1) as total FROM tb_user_agent_ref tua WHERE tua.agent_id ={0}'+
    
    ' UNION ALL SELECT truncate(IFNULL(a.money ,0)/1000000,2)  as total from tb_agent a  WHERE a.id = {0} ' +
    ' UNION ALL SELECT truncate(IFNULL(SUM(ta.money),0)/1000000,2)  as total FROM tb_agent_money_log  ta WHERE ta.agent_id = {0} AND ta.crate_time BETWEEN curdate() AND  subdate(curdate(),-1) '+
    ' UNION ALL SELECT truncate(IFNULL(SUM(t.money),0)/1000000,2) as total FROM tb_agent_money_log  t WHERE t.agent_id = {0} AND t.crate_time BETWEEN subdate(curdate(),date_format(curdate(),"%w")-1) AND  subdate(curdate(),date_format(curdate(),"%w")-8)';
    sql = sql.format(userId);
    if(role_id==1){
        sql = 'SELECT count(1) as total FROM tb_agent ta WHERE ta.role_id !=1 UNION all SELECT count(1) as total FROM t_users tu WHERE tu.userid NOT in (SELECT user_id from tb_agent)'+
        ' UNION ALL SELECT truncate(IFNULL(a.money ,0)/1000000,2)  as total from tb_agent a  WHERE a.id = {0} ' +
        ' UNION ALL SELECT truncate(IFNULL(SUM(ta.money),0)/1000000,2)  as total FROM tb_agent_money_log  ta WHERE ta.agent_id = {0} AND ta.crate_time BETWEEN curdate() AND  subdate(curdate(),-1) '+
        ' UNION ALL SELECT truncate(IFNULL(SUM(t.money),0)/1000000,2) as total FROM tb_agent_money_log  t WHERE t.agent_id = {0} AND t.crate_time BETWEEN subdate(curdate(),date_format(curdate(),"%w")-1) AND  subdate(curdate(),date_format(curdate(),"%w")-8)';
    }
    sql = sql.format(userId);
    console.info(sql);
    query(sql, function(err, rows, fields) {
        if (err) {
           // throw err;
           callback({code:2,data:err});
           return;
        }
        if(rows.length ==5){
            var player = rows[1].total;
            var agent = rows[0].total;
            var balance = rows[2].total;
            var todayBalance = rows[3].total;
            var weekBalance = rows[4].total;
            callback({code:0,data:{
                player:player,  //玩家数
                agent:agent,//代理数
                balance:balance,//可体现数据
                todayBalance:todayBalance,//今日业绩
                weekBalance:weekBalance //周业绩
            }});
        }else{
            callback({code:0,data:null});
        }
    });
};

//获取名下代理商数量
exports.get_agent_count = function(id,role_id,callback){
    callback = callback == null? nop:callback;
    if(id == null){
        callback({code:1,data:'params id is null'});
        return;
    }

    //var sql = 'SELECT count(1) as total FROM `tb_user` u WHERE u.puser_id = {0} and u.role_id IN (1,2,3,4)';
     var sql ='SELECT count(1) as total FROM tb_agent ta,t_users tu WHERE  ta.user_id = tu.userid AND ta.pid = {0} '; 
    sql = sql.format(id);
    if(role_id==1){
       sql = ' SELECT count(1) as total FROM tb_agent ta where ta.role_id !=1';
    }
    query(sql, function(err, rows, fields) {
        if (err) {
           // throw err;
           callback({code:2,data:err});
           return;
        }
        if(rows.length >0){
            callback({code:0,data:rows[0]});
        }else{
            callback({code:0,data:null});
        }
    });
};

//获取名下代理详情
exports.get_agent_list = function(userId,pageStart,pageLimit,role_id,callback){

    callback = callback == null? nop:callback;
    if(userId == null|| pageStart==null ||pageLimit ==null){
        callback({code:1,data:'params  is null'});
        return;
    }
   // var sql = 'SELECT u.id,u.user_wx_name,u.user_id,u.role_id,u.user_gold,u.user_wx_headimgurl,(select t.`name` from tb_role t where t.id = u.role_id) as role_name FROM `tb_user` u WHERE u.puser_id = {0} and u.game_id = {3} AND u.role_id IN (1,2,3,4)  ORDER BY u.role_id limit {1},{2}';
   var sql ='SELECT ta.id,tu.`name`,ta.invite_code,tu.userid,tu.gems,tu.headimg,truncate(ta.money/1000000,2) as money FROM tb_agent ta,t_users tu WHERE  ta.user_id = tu.userid AND ta.pid = {0} ORDER BY role_id limit {1},{2}'; 
   sql = sql.format(userId,pageStart,pageLimit);
   if(role_id==1){

      sql ='SELECT ta.id,truncate(ta.money/1000000,2) as money,ta.invite_code,tu.`name`,tu.userid,tu.gems,tu.headimg FROM tb_agent ta,t_users tu WHERE  ta.user_id = tu.userid AND ta.role_id !=1 ORDER BY role_id limit {0},{1}'; 
     sql = sql.format(pageStart,pageLimit);
   }
    console.log("获取代理 ： " +sql);
    query(sql, function(err, rows, fields) {
        if (err) {
           // throw err;
           callback({code:2,data:err});
           return;
        }
        if(rows.length >0){
             for(var i=0;i <rows.length; i++){
                rows[i].name = crypto.fromBase64(rows[i].name);
             }
            callback({code:0,data:rows});
        }else{
            callback({code:0,data:null});
        }
    });

};

//获取名下玩家数量
exports.get_player_count = function(id,role_id,callback){


    callback = callback == null? nop:callback;
    if(id == null){
        callback({code:1,data:'params id is null'});
        return;
    }

  //  var sql = 'SELECT count(1) as total FROM `tb_user` u WHERE u.puser_id = {0} and u.role_id =5';
  var sql ='SELECT count(1) as total from tb_agent ta ,tb_user_agent_ref tua WHERE  ta.id = tua.agent_id  AND ta.id ={0}' ;
  sql = sql.format(id);
  if(role_id==1){
      sql ='SELECT count(1) as total FROM t_users WHERE userid NOT in (SELECT user_id from tb_agent)' ;
  }

    
    query(sql, function(err, rows, fields) {
        if (err) {
           // throw err;
           callback({code:2,data:err});
           return;
        }
        if(rows.length >0){
            callback({code:0,data:rows[0]});
        }else{
            callback({code:0,data:null});
        }
    });

};

//获取名下玩家详情
exports.get_player_list = function(id,pageStart,pageLimit,role_id,callback){
callback = callback == null? nop:callback;
    if(id == null || pageStart==null || pageLimit ==null){
        callback({code:1,data:'params  is null'});
        return;
    }

   // var sql = 'SELECT u.id,u.user_wx_name,u.user_id,u.role_id,u.user_gold,u.user_wx_headimgurl,(select t.`name` from tb_role t where t.id = u.role_id) as role_name FROM `tb_user` u WHERE u.puser_id = {0} and u.game_id = {3} AND u.role_id =5 limit {1},{2}';
   var sql ="SELECT "
            +" ta.id,"
            +" tu.`name`,"
           +" tu.userid as user_id,"
           +" tu.gems,"
            +"tu.headimg"
       +" FROM "
          +"  tb_agent ta,tb_user_agent_ref tua,t_users tu"
       +" WHERE ta.id = tua.agent_id"
       +" AND tua.user_id = tu.userid"
       +" AND "
       +"     ta.id = tua.agent_id"
      +"  AND ta.id = {0}"
      +"  ORDER BY "
       +"     tu.gems"
      +"  LIMIT {1},{2}"

     sql = sql.format(id,pageStart,pageLimit);
      //管理员 特殊处理
    if(role_id==1){

       sql ="SELECT tu.`name`,tu.userid as user_id,tu.gems,tu.headimg FROM t_users tu WHERE userid NOT in (SELECT user_id from tb_agent) LIMIT {0},{1}";
       sql = sql.format(pageStart,pageLimit);
    }
   
    console.log("获取玩家 ： " +sql);
    query(sql, function(err, rows, fields) {
        if (err) {
           // throw err;
           callback({code:2,data:err});
           return;
        }
        if(rows.length >0){
             for(var i=0;i <rows.length; i++){
                rows[i].name = crypto.fromBase64(rows[i].name);
             }
            callback({code:0,data:rows});
        }else{
            callback({code:0,data:null});
        }
    });
};


//通过id判断用户是否存在 使用userid
exports.charge=function(param,callback){
     callback = callback == null? nop:callback;
    if(param == null){
        callback({code:1,data:'param  is null'});
        return;
    }

    var sql = 'CALL p_charge({0},{1},{2}, @a, @b);';
    sql = sql.format(param.sendId,param.receiveId,param.num);
    query(sql, function(err, rows, fields) {
        if (err) {
            //throw err;
          callback({code:2,data :err});
           return;
        }

      //  console.log(rows);
        if(rows.length == 0){
            callback({code:0 ,data :null});
            return;
        }

        callback({code:0,data:rows[0]});
    });  

};


//通过id判断用户是否存在
exports.set_agent=function(param,callback){
     callback = callback == null? nop:callback;
    if(param == null){
        callback({code:1,data:'param  is null'});
        return;
    }
    var sql = 'CALL p_set_agent({0},{1},@a, @b);';
     console.log(sql);
    sql = sql.format(param.pagentId,param.agentId);
    console.log(sql);
    query(sql, function(err, rows, fields) {
        if (err) {
            //throw err;
          callback({code:2,data :err});
           return;
        }

      //  console.log(rows);
        if(rows.length == 0){
            callback({code:0 ,data :null});
            return;
        }

        callback({code:0,data:rows[0]});
    });  

};


//绑定用户
exports.bind_user = function(userId,puserId,gameId,callback){

    callback = callback == null? nop:callback;
    if(userId == null || puserId ==null || gameId ==null){
        callback({code:1,data:'param  is null'});
        return;
    }
    var sql = 'CALL p_band({0},{1},{2}, @a, @b);';
    sql = sql.format(userId,puserId,gameId);
    console.log(sql);
    query(sql, function(err, rows, fields) {
        if (err) {
            //throw err;
          callback({code:2,data :err});
           return;
        }

        //console.log(rows);
        if(rows.length == 0){
            callback({code:0 ,data :null});
            return;
        }
        callback({code:0,data:rows[0]});
    });  
};


exports.updateGoldByUserId = function(userId,num,gameId,callback){
callback = callback == null? nop:callback;
    if(userId == null ){
        callback({code:1,data:'params  is null'});
        return;
    }
    var sql = 'UPDATE `tb_user` SET  `user_gold`={1} , `update_time` =NOW() WHERE `user_id`={0} and game_id = {2}';
    sql = sql.format(userId,num,gameId);
    console.info(sql);
    query(sql, function(err, rows, fields) {
        
        if (err) {
           // throw err;
           callback({code:2,data:err});
           return;
        }

        var affectedRows = rows.affectedRows;
        callback({code:0,data:affectedRows});
      
    });
};


//按时间查询
exports.getAgentBalance = function(params,callback){    
    callback = callback == null? nop:callback;
    if(params == null ){
            callback({code:1,data:'params  is null'});
            return;
    }
   
    var sql = 'SELECT t.user_id,DATE_FORMAT(t.crate_time,"%Y-%m-%d %H:%i:%s") as crate_time,truncate(t.fee/1000000,2) as fee,t.rate,truncate(t.money/1000000,2) as money,(SELECT u.`name` from t_users u WHERE u.userid  = t.resource_user_id ) as name from tb_agent_money_log t WHERE t.agent_id = {0} AND t.crate_time BETWEEN {1} AND subdate({2},-1) ';
     if(params.start_time==null || params.end_time==null){
     sql = 'SELECT t.user_id,DATE_FORMAT(t.crate_time,"%Y-%m-%d %H:%i:%s") as crate_time,truncate(t.fee/1000000,2) as fee,t.rate,truncate(t.money/1000000,2) as money,(SELECT u.`name` from t_users u WHERE u.userid  = t.resource_user_id ) as name from tb_agent_money_log t WHERE t.agent_id = {0} AND t.crate_time BETWEEN curdate() AND  subdate(curdate(),-1)';
    }
   
    sql = sql.format(params.agentId,"'"+params.start_time+"'","'"+params.end_time+"'");
    console.info(sql);

     query(sql, function(err, rows, fields) {
        if (err) {
           // throw err;
           callback({code:2,data:err});
           return;
        }
        if(rows.length >0){
             for(var i=0;i <rows.length; i++){
                rows[i].name = crypto.fromBase64(rows[i].name);
             }
            callback({code:0,data:rows});
        }else{
            callback({code:0,data:null});
        }
    });

};


exports.payAgent = function(param,callback){
callback = callback == null? nop:callback;
    if(param == null ){
        callback({code:1,data:'params  is null'});
        return;
    }
    var money = param.money * 1000000;
    var sql = 'UPDATE `tb_agent` SET  `money`= money - {0}, `update_time` =NOW() WHERE `user_id`={1} and money >= {2} ';
    sql = sql.format(money,param.childUserId,money);
    console.info("代理提现：",sql);
    query(sql, function(err, rows, fields) {
        
        if (err) {
           // throw err;
           callback({code:2,data:err});
           return;
        }

        var affectedRows = rows.affectedRows;
        if(affectedRows==1){
             sql = 'INSERT INTO `tb_agent_draw_log` ( `user_id`, `operate_id`, `money`, `crate_time`) VALUES ( {0}, {1}, {2}, now()); ';
             sql = sql.format(param.childUserId,param.agentId,money);
              console.info("代理提现 记录：",sql);
             query(sql, function(err, rows, fields) {
                 if (err) {
                    // throw err;
                    callback({code:2,data:err});
                    return;
                    }
                callback({code:0,data:affectedRows});
             });
            }else{
                 callback({code:3,data:'余额不足'});
            }
        
      
    });
};

//获取代理id下面的俱乐部id
exports.getClubList = function(id,role_id,callback){

    callback = callback == null? nop:callback;
    if(id == null|| role_id==null ){
        callback({code:1,data:'params  is null'});
        return;
    }
   //管理员 能查看所有的俱乐部
   var sql ='';
   if(role_id==1){
    sql = 'SELECT t.id,t.status,t.total_gems,t.club_id,t.club_name,t.gems,t.`limit`,t.current,(SELECT count(1) from tb_club_apply tc WHERE tc.club_id = t.id and tc.status=0) as audit_count FROM `tb_club` t   order by audit_count desc';
   }else{
     sql = 'SELECT t.id,t.status,t.total_gems,t.club_id,t.club_name,t.gems,t.`limit`,t.current,(SELECT count(1) from tb_club_apply tc WHERE tc.club_id = t.id and tc.status=0) as audit_count FROM `tb_club` t WHERE t.agent_id = {0} order by audit_count desc';
     sql = sql.format(id);
   }
    console.info("获取俱乐部list",sql);
    query(sql, function(err, rows, fields) {

        if (err) {
            callback({code:2,data:err});
             return;
        }

         if(rows.length >0){
             
            callback({code:0,data:rows});
        }else{
            callback({code:0,data:null});
        }
        

    });
};


//获取代理id下面的俱乐部id
exports.getClubApplyList = function(club_id,callback){

    callback = callback == null? nop:callback;
    if( club_id==null ){
        callback({code:1,data:'params  is null'});
        return;
    }
   
   var sql = 'SELECT t.id, t.user_id,u.headimg,c.club_name,u.`name`,DATE_FORMAT(t.apply_time,"%Y-%m-%d %H:%i:%s") as apply_time,t.`status` FROM `tb_club_apply` t,t_users u,tb_club c WHERE   t.user_id = u.userid AND t.club_id = c.id AND c.id= {0} AND t.status = 0';
    sql = sql.format(club_id);
   
    console.info("获取俱乐部待审核list",sql);
    query(sql, function(err, rows, fields) {

        if (err) {
            callback({code:2,data:err});
             return;
        }

         if(rows.length >0){
            if(rows.length >0){
             for(var i=0;i <rows.length; i++){
                rows[i].name = crypto.fromBase64(rows[i].name);
             }
            callback({code:0,data:rows});
        }
        }else{
            callback({code:0,data:null});
        }
        

    });
};

exports.getClubUserList = function(params,callback){

    callback = callback == null? nop:callback;
    if( params==null ){
        callback({code:1,data:'params  is null'});
        return;
    }
   
   var sql = 'SELECT tc.club_name,tu.name,tu.headimg,tu.userid,tu.gems,DATE_FORMAT(tcu.create_time,"%Y-%m-%d %H:%i:%s") as create_time FROM tb_club tc ,tb_club_user_ref tcu,t_users tu WHERE tc.id = tcu.club_id AND tcu.user_id = tu.userid AND tc.id = {0}  ORDER BY tcu.create_time  limit {1},{2} ';
    sql = sql.format(params.club_id,params.start,params.limit);
   
    console.info("获取俱乐部所有成员",sql);
    query(sql, function(err, rows, fields) {

        if (err) {
            callback({code:2,data:err});
             return;
        }

         if(rows.length >0){
            if(rows.length >0){
             for(var i=0;i <rows.length; i++){
                rows[i].name = crypto.fromBase64(rows[i].name);
             }
            callback({code:0,data:rows});
        }
        }else{
            callback({code:0,data:null});
        }
        

    });
};

exports.auditApply=function(apply_id,status,callback){

    callback = callback == null? nop:callback;
    if( apply_id==null ||status==null ){
        callback({code:1,data:'params  is null'});
        return;
    }

   var sql = 'CALL p_audit_club({0},{1}, @code, @msg)';
   sql = sql.format(apply_id,status);
   console.info("审核俱乐部申请",sql);
   query(sql, function(err, rows, fields) {

        if (err) {
            callback({code:2,data:err});
             return;
        }  
        callback({code:0,data:rows[0]});
    });

};

exports.createClub=function(params,callback){

    callback = callback == null? nop:callback;
    if( params == null){
        callback({code:1,data:'params  is null'});
        return;
    }


    //房卡数据必须大于100 
    //用户房卡需要大于俱乐部设置房卡
    //insert俱乐部数据
    //用户房卡做扣减

    var sql = 'CALL p_create_club({0},{1},{2},{3}, @code, @msg)';
   // sql = sql.format(openid,nickname,sex,headimgurl,role,unionid,agent);


   // var sql = 'INSERT INTO `tb_club` (`club_id`, `club_name`, `user_id`, `status`, `agent_id`,  `limit`, `gems`, `creat_time`) VALUES ({0}, {1}, {2}, 0, {3}, 500,{4}, now())';
    sql = sql.format("'"+params.club_name+"'",params.user_id,params.id,params.gems);
   
    console.info("创建俱乐部",sql);
     query(sql, function(err, rows, fields) {

        if (err) {
            callback({code:2,data:err});
             return;
        }
        callback({code:0,data:rows[0]});
    });

};
exports.delteFromClub=function(params,callback){

    callback = callback == null? nop:callback;
    if( params == null){
        callback({code:1,data:'params  is null'});
        return;
    }
    //自己创建的才能删除
    var sql = 'delete from tb_club_user_ref   WHERE club_id ={0} AND user_id = {1} AND (SELECT count(1) from tb_club tb WHERE tb.id = {0} AND tb.agent_id = {2} limit 1)=1 ';
    sql = sql.format(params.club_id,params.user_id,params.id);
   
    console.info("删除俱乐部成员",sql);
     query(sql, function(err, rows, fields) {

        if (err) {
            callback({code:2,data:err});
             return;
        }
        var affectedRows = rows.affectedRows;
        if(affectedRows==0){
            callback({code:3,data:affectedRows});
        }else{
            //俱乐部总人数 -1 
             sql="UPDATE tb_club t set t.current = t.current -1 ,update_time  = NOW() WHERE t.id ={0}";
             sql = sql.format(params.club_id);
             query(sql, function(err, rows, fields) {

                    if (err) {
                          callback({code:2,data:err});
                          return;
                          }
        
             callback({code:0,data:affectedRows});
        
       
      });
        }
             
        

       
    });

};
exports.getUserGems=function(params,callback){

    callback = callback == null? nop:callback;
    if( params == null){
        callback({code:1,data:'params  is null'});
        return;
    }
    //自己创建的才能删除
    var sql = 'select  gems from t_users where userid = {0} limit 1 ';
    sql = sql.format(params.user_id);
   
    console.info("查询用户房卡",sql);
     query(sql, function(err, rows, fields) {

        if (err) {
            callback({code:2,data:err});
             return;
        }

      
        callback({code:0,data:rows[0]});
       
      });
}
exports.updateClub=function(params,callback){

    callback = callback == null? nop:callback;
    if( params == null){
        callback({code:1,data:'params  is null'});
        return;
    }

    var sql = 'CALL p_update_club({0},{1},{2},{3}, @code, @msg)';
    sql = sql.format(params.club_id,params.user_id,params.charge_type,params.gems);
   
    console.info("修改俱乐部",sql);
     query(sql, function(err, rows, fields) {

        if (err) {
            callback({code:2,data:err});
             return;
        }
        callback({code:0,data:rows[0]});
    });

};
exports.editAgentPay=function(params,callback){

    callback = callback == null? nop:callback;
    if( params == null){
        callback({code:1,data:'params  is null'});
        return;
    }

    var sql = "UPDATE `tb_agent` SET  `name`='{0}', `mobile`='{1}', `alipay_account`='{2}',  `update_time`=NOW() WHERE `id`={3}";
    sql = sql.format(params.name,params.mobile,params.alipayAccount,params.id);
   
    console.info("修改代理信息",sql);
     query(sql, function(err, rows, fields) {

        if (err) {
            callback({code:2,data:err});
             return;
        }
        var affectedRows = rows.affectedRows;
        callback({code:0,data:affectedRows});
    });

};
exports.getAgentPayInfo=function(params,callback){

    callback = callback == null? nop:callback;
    if( params == null){
        callback({code:1,data:'params  is null'});
        return;
    }

    var sql = 'SELECT t.`name`,t.alipay_account as alipayAccount,t.mobile,t.id FROM `tb_agent` t WHERE t.id ={0} LIMIT 1';
    sql = sql.format(params.id);
   
    console.info("获取代理商联系信息",sql);
     query(sql, function(err, rows, fields) {

        if (err) {
            callback({code:2,data:err});
             return;
        }
        
      if(rows.length == 0){
            callback({code:0 ,data :null});
            return;
        }

        callback({code:0,data:rows[0]});
    });

};


var getNowFormatDate=function (){
        var date = new Date();
        var seperator1 = "-";
        var seperator2 = ":";
        var month = date.getMonth() + 1;
        var strDate = date.getDate();
        if (month >= 1 && month <= 9) {
            month = "0" + month;
        }
        if (strDate >= 0 && strDate <= 9) {
            strDate = "0" + strDate;
        }
        var currentdate = date.getFullYear() + seperator1 + month + seperator1 + strDate
                + " " + date.getHours() + seperator2 + date.getMinutes()
                + seperator2 + date.getSeconds();
        return currentdate;
    };



exports.getClubRoom = function(params,callback){

    callback = callback == null? nop:callback;
    if( params==null ){
        callback({code:1,data:'params  is null'});
        return;
    }
   
   var sql = " SELECT t.id,t.club_id,DATE_FORMAT((select from_unixtime(t.create_time)),'%Y-%m-%d %H:%i:%s')as create_time,t.base_info,"+
   "  t.user_id0,t.user_id1,t.user_id2,t.user_id3,t.user_score0,t.user_score1,t.user_score2,t.user_score3,"+
   "  t.user_name0,t.user_name1,t.user_name2,t.user_name3,t.num_of_turns,t.base_info"+
  "   from t_rooms_bak t WHERE t.club_id = {0} AND t.`status` =2 AND t.num_of_turns>0 AND t.create_time BETWEEN (select unix_timestamp('{1}')) AND (select unix_timestamp(subdate('{2}',-1)))" ;
    sql = sql.format(params.club_id,params.startTime,params.endTime);
   
    console.info("获取俱乐部房间信息",sql);
    query(sql, function(err, rows, fields) {

        if (err) {
            callback({code:2,data:err});
             return;
        }

         if(rows.length >0){
            if(rows.length >0){
             for(var i=0;i <rows.length; i++){
                //遍历计算每个的房卡数和大赢家数据
                var arr = new Array(rows[i].user_score0,rows[i].user_score1,rows[i].user_score2,rows[i].user_score3);
                
                 arr.sort(function(a,b){
                     return b-a});
                var maxScore = arr[0];
                //平局 没有大赢家
                if(maxScore == 0 ){
                    rows[i].nowin = true;
                }else{
                     rows[i].nowin = false;

                    if(maxScore == rows[i].user_score0){
                    rows[i].user_win0=true;
                }else{
                    rows[i].user_win0=false;
                }
                 if(maxScore == rows[i].user_score1){
                    rows[i].user_win1=true;
                }else{
                    rows[i].user_win1=false;
                }
                 if(maxScore == rows[i].user_score2){
                    rows[i].user_win2=true;
                }else{
                    rows[i].user_win2=false;
                } 
                if(maxScore == rows[i].user_score3){
                      rows[i].user_win3=true; 
                }else{
                    rows[i].user_win3=false;
                }

                }

                rows[i].gems = JSON.parse(rows[i].base_info).cost;
                rows[i].base_info="";
                rows[i].user_name0 = crypto.fromBase64(rows[i].user_name0);
                rows[i].user_name1 = crypto.fromBase64(rows[i].user_name1);
                rows[i].user_name2 = crypto.fromBase64(rows[i].user_name2);
                rows[i].user_name3 = crypto.fromBase64(rows[i].user_name3);
                
             }
            callback({code:0,data:rows});
        }
        }else{
            callback({code:0,data:null});
        }
        

    });
};

exports.query = query;